package JDBC;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;

import Model.Eleve;
import Model.Stage;
import Model.Stage;

public class Stage_Afficher 

{
	public ArrayList<Stage> afficherStage() 
	{
		ArrayList<Stage> mesStage = new ArrayList<Stage>();
		
		try 
		{

			connexionBDD a = new connexionBDD();
			Connection conn=a.connect();

			//Création d'un objet Statement
			Statement state = conn.createStatement();
			Statement state2 = conn.createStatement();
			Statement state3 = conn.createStatement();
			Statement state4 = conn.createStatement();
			Statement state5 = conn.createStatement();
			Statement state6 = conn.createStatement();

			//L'objet ResultSet contient le résultat de la requête SQL
			ResultSet result = state.executeQuery("SELECT * FROM stage ORDER BY id_stage");
			
			
			
			while(result.next())
			{	
				Stage stage = new Stage();
				stage.setIdStage(Integer.parseInt(result.getObject(1).toString()));
				stage.setDateStage(result.getObject(2).toString());
				stage.setDuree(Integer.parseInt(result.getObject(3).toString()));
				stage.setHeureDebut(result.getObject(4).toString());
				stage.setHeureFin(result.getObject(5).toString());
				stage.setId_Lieu(Integer.parseInt(result.getObject(6).toString()));
				
				ResultSet result6 = state6.executeQuery("SELECT ville FROM lieu where id_lieu="+Integer.parseInt(result.getObject(6).toString()));
				
				while(result6.next())
				{
					stage.setVille(result6.getObject(1).toString());
				}
				
				result6.close();
				
				ResultSet result4 = state4.executeQuery("SELECT nom_prof, prenom_prof FROM professeur where id_professeur="+Integer.parseInt(result.getObject(7).toString()));
				
				while(result4.next())
				{
					String[] prof = new String[2];
					prof[0]=result4.getObject(1).toString();
					prof[1]=result4.getObject(2).toString();
					stage.setProf(prof);
				}
				
				result4.close();
				
				ResultSet result2 = state2.executeQuery("SELECT libelle_mat FROM matiere where id_matiere="+Integer.parseInt(result.getObject(8).toString()));
				
				while(result2.next())
				{
					stage.setMatiere(result2.getObject(1).toString());
				}
				
				result2.close();
				
				ResultSet result3 = state3.executeQuery("SELECT libelle_niveau FROM niveau where id_niveau="+Integer.parseInt(result.getObject(9).toString()));
				
				while(result3.next())
				{
					stage.setNiveau(result3.getObject(1).toString());
				}
				
				result3.close();
				
			
				
				mesStage.add(stage);
				stage.toString();
				
			}
			
			result.close();
			state.close();
		} 

		catch (Exception e) 
		{
			e.printStackTrace();
		}	
		return mesStage;

	}
	
	public Stage afficherStage(int idStage)
	{
		Stage stage = new Stage();
		String adresse=null;
		int idLieu=0;
		try 
		{
			connexionBDD a = new connexionBDD();
			Connection conn=a.connect();

			//Création d'un objet Statement
			Statement state = conn.createStatement();
			Statement state2 = conn.createStatement();
			Statement state3 = conn.createStatement();
			Statement state4 = conn.createStatement();
			Statement state5 = conn.createStatement();
			Statement state6 = conn.createStatement();
			Statement state7= conn.createStatement();
			//L'objet ResultSet contient le résultat de la requête SQL
			ResultSet result = state.executeQuery("SELECT * FROM stage WHERE id_stage="+idStage);
			
			while(result.next())
			{	
				stage.setIdStage(Integer.parseInt(result.getObject(1).toString()));
				stage.setDateStage(result.getObject(2).toString());
				stage.setDuree(Integer.parseInt(result.getObject(3).toString()));
				stage.setHeureDebut(result.getObject(4).toString());
				stage.setHeureFin(result.getObject(5).toString());
				
				idLieu=Integer.parseInt(result.getObject(6).toString());
				stage.setId_Lieu(idLieu);
				
				ResultSet result7=state7.executeQuery("SELECT numero, rue, complement, codepostal, ville FROM lieu WHERE id_lieu="+idLieu);
				while(result7.next())
				{
					adresse=result7.getObject(1).toString()+" "+result7.getObject(2).toString()+" "+result7.getObject(3).toString()+" "+result7.getObject(4).toString()+" "+result7.getObject(5).toString()+" ";
					
				}
				result7.close();
				stage.setAdresse(adresse);
				ResultSet result6 = state6.executeQuery("SELECT ville FROM lieu where id_lieu="+Integer.parseInt(result.getObject(6).toString()));
				
				while(result6.next())
				{
					stage.setVille(result6.getObject(1).toString());
				}
				
				result6.close();
				
				
				stage.setId_Prof(Integer.parseInt(result.getObject(7).toString()));
				ResultSet result4 = state4.executeQuery("SELECT nom_prof, prenom_prof FROM professeur where id_professeur="+Integer.parseInt(result.getObject(7).toString()));
				
				while(result4.next())
				{
					String[] prof = new String[2];
					prof[0]=result4.getObject(1).toString();
					prof[1]=result4.getObject(2).toString();
					stage.setProf(prof);
				}
				
				result4.close();
				
				stage.setId_Mat(Integer.parseInt(result.getObject(8).toString()));
				ResultSet result2 = state2.executeQuery("SELECT libelle_mat FROM matiere where id_matiere="+Integer.parseInt(result.getObject(8).toString()));
				
				while(result2.next())
				{
					stage.setMatiere(result2.getObject(1).toString());
				}
				
				result2.close();
				
				stage.setId_Niv(Integer.parseInt(result.getObject(9).toString()));
				ResultSet result3 = state3.executeQuery("SELECT libelle_niveau FROM niveau where id_niveau="+Integer.parseInt(result.getObject(9).toString()));
				
				while(result3.next())
				{
					stage.setNiveau(result3.getObject(1).toString());
				}
				
				result3.close();
				
				
				stage.toString();
				
			}
			
			result.close();
			state.close();
		}
		catch (Exception e) 
		{
			e.printStackTrace();
		}
		
		return stage;
	}
	
	public ArrayList<Eleve> afficherEleve(int idStage)
	{
		ArrayList<Eleve> mesEleves=new ArrayList<Eleve>();
		try
		{
			connexionBDD a = new connexionBDD();
			Connection conn=a.connect();
			Statement state = conn.createStatement();
			Statement state2 = conn.createStatement();
			ResultSet result = state.executeQuery("SELECT * FROM eleves_stage WHERE id_stage="+idStage);
			
			while(result.next())
			{
				Eleve e=new Eleve();
				int idEleve=0;
				idEleve=Integer.parseInt(result.getObject(1).toString());
				ResultSet result2 = state2.executeQuery("SELECT * FROM eleve WHERE id_eleve="+idEleve);
				while(result2.next())
				{
					e.setIdEleve(Integer.parseInt(result2.getObject(1).toString()));
					e.setNom(result2.getObject(2).toString());
					e.setPrenom(result2.getObject(3).toString());
					e.setSexe(result2.getObject(4).toString());
					e.setNaissance(result2.getObject(5).toString());
					e.setEmail(result2.getObject(6).toString());
					mesEleves.add(e);
				}
				result2.close();
				

			}
			result.close();
			state.close();
		}
		catch (Exception e) 
		{
			e.printStackTrace();
		}
		return mesEleves;
	}

	

}
